package com.example.datatransfer.jdbc;


import com.example.datatransfer.callback.SourceCallback;
import com.example.datatransfer.entity.FieldInfo;
import com.example.datatransfer.entity.TableInfo;
import com.example.datatransfer.enums.DataTypeEnum;

import java.sql.*;
import java.util.*;


public class JdbcSourceTool {
	

	 static final String JDBC_DRRIVER = "com.mysql.jdbc.Driver";

	 static final String DB_URL = "jdbc:mysql://192.168.1.67:3306/cust_02";

	 static final String USER_NAME = "root";

	 static final String PASSWORD = "root";

	private JdbcConfig config;
	 
	 private JdbcSourceTool() {
		 try {
			Class.forName(JDBC_DRRIVER);
			config = new JdbcConfig(DB_URL,USER_NAME,PASSWORD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	 }
	 private static class Holder{
		 private static JdbcSourceTool instance = new JdbcSourceTool();
	 }
	 
	 public static JdbcSourceTool getInstance() {
		 return Holder.instance;
	 }
	 
	 private Connection createConnection() {
		 Connection conn = null;
		 try {
			 conn = DriverManager.getConnection(config.getDbUrl(), config.getUserName(), config.getPassword());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return conn;
	 }
	 
	 public <T> T execute(PrepareOption<T> prepareOption, String sql) {
		 
		 Connection conn = null;
		 PreparedStatement pstmt = null;
		 try {
			 
			 conn = createConnection();
			 pstmt = conn.prepareStatement(sql);
			 return prepareOption.doPreparedStatement(pstmt);
		 }catch(Exception e) {
			 e.printStackTrace();

		 }finally {
			 try {
			 	if(null != pstmt){
					pstmt.close();
				}
				 if(null != conn){
					 conn.close();
				 }

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 }
		 return null;
		 
	 }

	 private String getCommentForTable(String tableName){
	 	//table_comment 表的注释和备注；information_schema.TABLES 数据库中的表；TABLE_SCHEMA 数据表所属的数据库名；select database() 查询当前所使用的数据库；
	 	String sql = "select table_comment from information_schema.TABLES where TABLE_SCHEMA = (select database()) and TABLE_NAME = ?";
	 	String comment = execute(new PrepareOption<String>() {
			@Override
			public String doPreparedStatement(PreparedStatement pstmt) throws SQLException {
				String comment = "";
				pstmt.setString(1,tableName);
				ResultSet resultSet = pstmt.executeQuery();
				while (resultSet.next()){
					comment = resultSet.getString(1);

				}
				return comment;
			}
		},sql);
	 	return comment;
	 }

	 private List<FieldInfo> getFieldInfoList(String tableName,SourceCallback callback){
		 Set<String> set = new HashSet<>();
	 	String sql = "select COLUMN_NAME,IS_NULLABLE,DATA_TYPE," +
				" COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS where TABLE_NAME = ?";
		 return execute(new PrepareOption<List<FieldInfo>>() {
			@Override
			public List<FieldInfo> doPreparedStatement(PreparedStatement pstmt) throws SQLException {
				List<FieldInfo> fieldInfoList = new ArrayList<>();
				pstmt.setString(1, tableName);
				ResultSet resultSet = pstmt.executeQuery();
				while (resultSet.next()) {

					FieldInfo fieldInfo = new FieldInfo();
					String columnName = resultSet.getString(1);
					fieldInfo.setColumnName(columnName);
					fieldInfo.setIsNullable(resultSet.getString(2));
					fieldInfo.setDataType(resultSet.getString(3));
					fieldInfo.setColumnType(resultSet.getString(4));
					fieldInfo.setColumnKey(resultSet.getString(5));
					fieldInfo.setColumnComment(resultSet.getString(6));
					if (!set.contains(columnName)) {
						set.add(columnName);
						fieldInfoList.add(fieldInfo);
					}

				}
				String msg = "表名【" + tableName + "】 字段 【" + fieldInfoList + "】";
				if (null != callback) {
					callback.onReadAllField(msg);
				}
				return fieldInfoList;
			}
		},sql);

	 }

//	 public TableInfo getTableInfo(JdbcConfig config,String tableName, SourceCallback callback){
//	 	if(null != config){
//			this.config = config;
//		}
//
//	 	TableInfo tableInfo = new TableInfo();
//	 	String comment = getCommentForTable(tableName);
//	 	List<FieldInfo> fieldInfoList = getFieldInfoList(tableName,callback);
//	 	tableInfo.setFieldList(fieldInfoList);
//	 	tableInfo.setNameen(tableName);
//	 	tableInfo.setNamecn(comment);
//	 	List<Map<String,Object>> mapList = getSourceTableData(tableInfo,callback);
//	 	tableInfo.setMapList(mapList);
//		if(null != callback){
//			callback.onMakeTableInfo("表名【"+tableName+"】结构和数据组装完毕！");
//		}
//	 	return tableInfo;
//	 }

	//自己测试用 这里的问题是 在for循环中return 只会return第一个值因为遇到return就不会继续遍历了，在for循环外面遍历 只会返回集合的最后一个值。解决的办法是把TableInfo
	//改成List<TableInfo> 类型的就好了
	public TableInfo getTableInfo(JdbcConfig config, String tableName, SourceCallback callback){
		if(null != config){
			this.config = config;
		}

		TableInfo tableInfo = new TableInfo();
			String comment = getCommentForTable(tableName);
			List<FieldInfo> fieldInfoList = getFieldInfoList(tableName, callback);
			tableInfo.setFieldList(fieldInfoList);
			tableInfo.setNameen(tableName);
			tableInfo.setNamecn(comment);
			List<Map<String, Object>> mapList = getSourceTableData(tableInfo, callback);
			tableInfo.setMapList(mapList);
			if (null != callback) {
				callback.onMakeTableInfo("表名【" + tableName + "】结构和数据组装完毕！");
			}
		return tableInfo;
	}

	 private List<Map<String,Object>> getSourceTableData(TableInfo tableInfo,SourceCallback callback){
	 	String tableName = tableInfo.getNameen();
	 	List<FieldInfo> fieldInfoList = tableInfo.getFieldList();
	 	String sql = "select * from "+ tableName;
	 	return  execute(new PrepareOption<List<Map<String,Object>>>() {
			@Override
			public List<Map<String,Object>> doPreparedStatement(PreparedStatement pstmt) throws SQLException {
				List<Map<String,Object>> mapList = new ArrayList<>();
				ResultSet resultSet = pstmt.executeQuery();
				while (resultSet.next()){
					Map<String,Object> map = new HashMap<>();
					for(FieldInfo fieldInfo:fieldInfoList){
						String columnName = fieldInfo.getColumnName();
						String dateType = fieldInfo.getDataType();

						if(DataTypeEnum.VARCHAR.value.equals(dateType)){
							map.put(columnName,resultSet.getString(columnName));
						}else if(DataTypeEnum.DATE.value.equals(dateType)){
							map.put(columnName,resultSet.getDate(columnName));
						}else if(DataTypeEnum.TIME.value.equals(dateType)){
							map.put(columnName,resultSet.getTime(columnName));
						}else if(DataTypeEnum.DATETIME.value.equals(dateType)){
							map.put(columnName,resultSet.getDate(columnName));
						}else if(DataTypeEnum.DECIMAL.value.equals(dateType)){
							map.put(columnName,resultSet.getBigDecimal(columnName));
						}else if(DataTypeEnum.DOUBLE.value.equals(dateType)){
							map.put(columnName,resultSet.getDouble(columnName));
						}else if(DataTypeEnum.FLOAT.value.equals(dateType)){
							map.put(columnName,resultSet.getFloat(columnName));
						}else if(DataTypeEnum.INT.value.equals(dateType)){
							map.put(columnName,resultSet.getInt(columnName));
						}else if(DataTypeEnum.MEDIUMTEXT.value.equals(dateType)){
							map.put(columnName,resultSet.getString(columnName));
						}else if(DataTypeEnum.TEXT.value.equals(dateType)){
							map.put(columnName,resultSet.getString(columnName));
						}else if(DataTypeEnum.CHAR.value.equals(dateType)){
							map.put(columnName,resultSet.getString(columnName));
						}else if(DataTypeEnum.LONGTEXT.value.equals(dateType)){
							map.put(columnName,resultSet.getString(columnName));
						}else{
							System.out.println(dateType);
						}
					}
					mapList.add(map);
				}
				String msg = "表名【"+tableName+"】数据获取成功！";
				if(null != callback){
					callback.onReadData(msg);
				}
				return mapList;
			}
		},sql);


	 }

	 public List<String> getTableNameList(JdbcConfig config){
		 if(null != config){
			 this.config = config;
		 }


	 	String sql = "select table_name from information_schema.TABLES where TABLE_SCHEMA = (select database()) ";
	 	return execute(new PrepareOption<List<String>>() {
			@Override
			public List<String> doPreparedStatement(PreparedStatement pstmt) throws SQLException {
				List<String> nameList = new ArrayList<>();
				ResultSet resultSet = pstmt.executeQuery();
				while (resultSet.next()){
					String tableName = resultSet.getString(1);
					nameList.add(tableName);
				}

				return nameList;
			}
		},sql);
	 }
	 public static void main(String args[]) {
		 
	 }
	 

}
